package my.jbf.ALLINONE.DBtest;

import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
//import com.example.domain.Person;
public class PersonService
{
	private DBOperateHelper dbOperateHelper;
	public PersonService(Context context)
	{
		this.dbOperateHelper = new DBOperateHelper(context);
	}
	/**
	 * 保存记录
	 * @param person
	 */
	public void save(Person person)
	{
		//得到数据库实例，里面封装了数据库操作方法
		SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
		//sqldb.execSQL("insert into person(name,phone) values('"+person.getName()+"','"+person.getPhone()+"')");
		//利用占位符可以避免注入，但是注意数组参与与占位符对应的字段要一一对应
		sqldb.execSQL("insert into person(name,phone,amount) values(?,?,?)", new Object[]{person.getName(),person.getPhone(),person.getAmount()});
		//关闭数据库
		sqldb.close();
	}
	/**
	 * 删除记录
	 * @param id
	 */
	public void delete(Integer id)
	{
		SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
		sqldb.execSQL("delete from person where personid=?", new Object[]{id});
		sqldb.close();
	}
	/**
	 * 更新记录
	 * @param person
	 */
	public void update(Person person)
	{
		SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
		sqldb.execSQL("update person set name=?,phone=?,amount=? where personid=?", new Object[]{person.getName(),person.getPhone(),person.getAmount(),person.getId()});
		sqldb.close();
	}
	/**
	 * 通过ID查询记录
	 * @param id
	 * @return
	 */
	public Person find(Integer id)
	{
		/**
		 * getWritableDatabase 与 getReadableDatabase 的区别：
		 * getReadableDatabase会先返回getWritableDatabase(可写)，如果调用getWritableDatabase失败
		 * 则才会调用getReadableDatabase后续方法，使数据库只读
		 * 当写入的数据超过数据库大小则调用getWritableDatabase会失败
		 * 所以只读时则可以使用此方法，其它情况(只要不是超过数据库大小)也可以使用此方法
		 */
		SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
		Cursor cursor = sqldb.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
		int personid;
		String name;
		String phone;
		int amount;
		Person person = null;
		if (cursor.moveToFirst())
		{
			personid = cursor.getInt(cursor.getColumnIndex("personid"));
			name = cursor.getString(cursor.getColumnIndex("name"));
			phone = cursor.getString(cursor.getColumnIndex("phone"));
			amount = cursor.getInt(cursor.getColumnIndex("amount"));
			person = new Person(personid, name, phone, amount);
		}
		cursor.close();
		return person;
	}
	/**
	 * 返回指定长度记录，limit 3,5,适用于分页
	 * @param offset 起始
	 * @param maxResult 长度
	 * @return
	 */
	public List<Person> getScrollData(int offset, int maxResult)
	{
		SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
		Cursor cursor = sqldb.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		int personid;
		String name;
		String phone;
		int amount;
		Person person = null;
		List<Person> persons = new ArrayList<Person>();
		while (cursor.moveToNext())
		{
			personid = cursor.getInt(cursor.getColumnIndex("personid"));
			name = cursor.getString(cursor.getColumnIndex("name"));
			phone = cursor.getString(cursor.getColumnIndex("phone"));
			amount = cursor.getInt(cursor.getColumnIndex("amount"));
			person = new Person(personid, name, phone, amount);
			persons.add(person);
		}
		cursor.close();
		return persons;
	}
	/**
	 * 返回cursor
	 * @param offset 起始
	 * @param maxResult 长度
	 * @return
	 */
	public Cursor getCursorScrollData(int offset, int maxResult)
	{
		SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
		Cursor cursor = sqldb.rawQuery("select personid as _id,name,phone,amount from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		return cursor;
	}
	/**
	 * 返回总记录数
	 * @return
	 */
	public long getCount()
	{
		SQLiteDatabase sqldb = dbOperateHelper.getReadableDatabase();
		Cursor cursor = sqldb.rawQuery("select count(*) from person", null);
		//该查询语句值返回一条语句
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}
	public void payment()
	{
		SQLiteDatabase sqldb = dbOperateHelper.getWritableDatabase();
		sqldb.beginTransaction();//开启事务
		try
		{
			sqldb.execSQL("update person set amount = amount -10 where personid=1");
			sqldb.execSQL("update person set amount = amount + 10 where personid=2");
			sqldb.setTransactionSuccessful();//设置事务标志位true
		}
		finally
		{
			//结束事务：有两种情况：commit\rollback，事务提交或者回滚是由事务的标识决定的
			//事务为ture则提交，事务为flase则回滚，默认为false
			sqldb.endTransaction();
		}
	}
}

